import contextlib
from datetime import date,datetime
from typing import List
import threading
from sqlalchemy import (
    create_engine,
    Column,
    Integer,
    DateTime,
    Boolean,
    String, TIMESTAMP
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from config import config  # config模块里有自己写的配置，我们可以换成别的，注意下面用到config的地方也要一起换

engine = create_engine(
    config["SQLALCHEMY_DATABASE_URI"],  # SQLAlchemy 数据库连接串，格式见下面
    echo=config["SQLALCHEMY_ECHO"],  # 是不是要把所执行的SQL打印出来，一般用于调试
    # pool_size=int(config["SQLALCHEMY_POOL_SIZE"]),  # 连接池大小
    # max_overflow=int(config["SQLALCHEMY_POOL_MAX_SIZE"]),  # 连接池最大的大小
    # pool_recycle=int(config["SQLALCHEMY_POOL_RECYCLE"]),  # 多久时间回收连接
)
Session = sessionmaker(bind=engine)
Base = declarative_base(engine)

lock = threading.Lock()
# Dependency
def db():
    db = Session()
    try:
        yield db
    finally:
        db.close()


class BaseMixin:
    """model的基类,所有model都必须继承"""
    id = Column(Integer, primary_key=True)
    created_at = Column(DateTime, nullable=False, default=datetime.now)
    updated_at = Column(DateTime, nullable=False, default=datetime.now, onupdate=datetime.now,
                        index=True)


@contextlib.contextmanager
def get_session():
    s = Session()
    try:
        yield s
        s.commit()
    except Exception as e:
        s.rollback()
        raise e
    finally:
        s.close()


class Config(Base, BaseMixin):
    __tablename__ = "config"
    cut_rim = Column(Integer, nullable=False)
    rotate = Column(Boolean, nullable=False, default=True)
    utilizationrate = Column(Integer, nullable=False)
    length = Column(Integer, nullable=False)
    width = Column(Integer, nullable=False)
    batch_size = Column(Integer, nullable=False)


class OrderPooled(Base, BaseMixin):
    __tablename__ = "orderpooled"
    depart_num = Column(String(50))
    dailyplan = Column(String(50))
    production_type = Column(String(50))
    material_type = Column(String(50))
    material_no = Column(String(50))
    order_number = Column(String(50))
    optimization_type = Column(String(50))
    length = Column(Integer, nullable=False)
    width = Column(Integer, nullable=False)
    texture = Column(Integer, nullable=False)
    process = Column(String(50))
    section_name = Column(String(50))
    tricks = Column(String(50))
    work_order = Column(String(50))
    quantity = Column(Integer, nullable=False)
    bar_code = Column(String(50))
    datatime = Column(TIMESTAMP)
    batch_number = Column(String(255))
    utilization_rate = Column(String(255))
    cutting_time = Column(DateTime, nullable=False, default=datetime.now)
    specification = Column(String(255))
    square_workpieces = Column(String(255))
    total_square = Column(String(255))


def example():
    with get_session() as s:
        return s.query(Config).one()


def get_config() -> Config:
    return Session().query(Config).first()


def get_order_pooled() -> List[OrderPooled]:
    return Session().query(OrderPooled).all()


def get_order_pooled_count(depart_num: List[str], optimization_type: List[str], dates: List[date]) -> int:
    return Session() \
        .query(OrderPooled) \
        .filter(
        OrderPooled.depart_num.in_(depart_num),
        OrderPooled.optimization_type.in_(optimization_type),
        OrderPooled.datatime.in_(dates)
    ).count()

def save_order_pooled(items:List[OrderPooled])->int:
    lock.acquire()
    with get_session() as s:
        s.save(items)
        s.commit()
    lock.release()
    return len(items)

if __name__ == '__main__':
    print(get_config().width)
    orders = get_order_pooled()
    for o in orders:
        print(o)
